<?php
	function detail_kelas($nis,$id_tapel){
		$sql="SELECT
					`atur_kelas`.`id_mr_th_ajaran`
					, `mr_siswa`.`nm_lengkap`
					, `mr_siswa`.`nis`
					, `mr_lembaga`.`nama_lembaga`
					, `mr_kelas`.`kelas`
				FROM
					`bagi_kelas`
					left JOIN `atur_kelas` 
						ON (`bagi_kelas`.`id_atur_kelas` = `atur_kelas`.`id_atur_kelas`)
					left JOIN `mr_siswa` 
						ON (`bagi_kelas`.`nis` = `mr_siswa`.`nis`)
					left JOIN `mr_lembaga` 
						ON (`mr_siswa`.`id_lembaga` = `mr_lembaga`.`id_lembaga`)
					left JOIN `mr_kelas` 
						ON (`atur_kelas`.`id_mr_kelas` = `mr_kelas`.`id_mr_kelas`)
				WHERE (`atur_kelas`.`id_mr_th_ajaran` =".$id_tapel."
					AND `mr_siswa`.`nis` ='".$nis."');";
	$hasil=get_arr_query_row($sql);
		return $hasil;
	}
	function ambil_bulan($bln) {
		switch($bln) {
		case 1 : $nama_bulan = "Januari" ; break;
		case 2 : $nama_bulan = "Februari" ; break;
		case 3 : $nama_bulan = "Maret" ; break;
		case 4 : $nama_bulan = "April" ; break;
		case 5 : $nama_bulan = "Mei" ; break;
		case 6 : $nama_bulan = "Juni" ; break;
		case 7 : $nama_bulan = "Juli" ; break;
		case 8 : $nama_bulan = "Agustus" ; break;
		case 9 : $nama_bulan = "September" ; break;
		case 10 : $nama_bulan = "Oktober" ; break;
		case 11 : $nama_bulan = "November" ; break;
		case 12 : $nama_bulan = "Desember" ; break;
		default:$nama_bulan="";
		}
		return $nama_bulan ;
	}
	function tgl_indonesia($tgl) {
		$tanggal=substr($tgl,8,2);
		$bulan=ambil_bulan(substr($tgl,5,2));
		$tahun=substr($tgl,0,4);
		return $tanggal." ".$bulan." ".$tahun ;
	}
	//buat array
	function get_arr_query($sql){
		$query=mysql_query($sql);
		while ($hasil = mysql_fetch_assoc($query)){
			$result_array[] = $hasil;
		}
		return $result_array;
	}
	
	function get_detail_wali($nis){
		$sql="SELECT * FROM mr_ortuwali WHERE nis='".$nis."' and tlp <> '' ORDER BY FIELD( hubungan, 'Ibu', 'Ayah', 'Wali' )";
		//echo $sql;
		$hasil=get_arr_query_row($sql);
		return $hasil;
	}
	
	function get_detail_wali_email($nis){
		$sql="SELECT * FROM mr_ortuwali WHERE nis='".$nis."' ORDER BY FIELD( hubungan, 'Ayah', 'Ibu', 'Wali' )";
		//echo $sql;
		$hasil=get_arr_query_row($sql);
		return $hasil;
	}
	
	function isi_sms($pesan){
		$sql="insert into coba_sms(isi_pesan) values('".$pesan."')";
		$query=mysql_query($sql);
	}
	
	function kirim_email($email,$pesan){
		$url="http://rai.asia/email_saf/kirim_email.php";
		$ch = curl_init($url);

		$nvp = "&alamat_email=".$email."&pesan=".$pesan;
		//curl_setopt($ch, CURLOPT_COOKIE, session_name() . '=' . session_id());
		curl_setopt($ch, CURLOPT_POSTFIELDS, $nvp);
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

		$respon= curl_exec($ch);
		//return $respon;
	}
	
	function kirim_sms($no_hp,$pesan){
		$url="http://localhost/sms_saf/kirim_sms.php";
		$ch = curl_init($url);

		$nvp = "&no_hp=".$no_hp."&sms=".$pesan;
		//curl_setopt($ch, CURLOPT_COOKIE, session_name() . '=' . session_id());
		curl_setopt($ch, CURLOPT_POSTFIELDS, $nvp);
		//curl_setopt($ch, CURLOPT_COOKIEFILE, '/tmp/cookies.txt'); // set cookie file to given file
		//curl_setopt($ch, CURLOPT_COOKIEJAR, '/tmp/cookies.txt'); 
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);

		$respon= curl_exec($ch);
		//$data=array('isi_pesan'=>$pesan);
		//$insert=$this->db->insert('coba_sms',$data);
		//return $respon;
	}
	
	function get_arr_query_row($sql){
		$query=mysql_query($sql);
		$hasil = mysql_fetch_assoc($query);
		return $hasil;
	}

	function get_arr_row($sql){
		$query=mysql_query($sql);
		while ($hasil = mysql_fetch_row($query)){
			$result_array[] = $hasil;
		}
		return $result_array;
	}
	function get_tapel_aktif(){
		$sql="select * from mr_th_ajaran where aktif='ya'";
		$query=mysql_query($sql);
		$num_row=mysql_num_rows($query);
		if ($num_row>0){
			return get_arr_query_row($sql);
		}else{
			return array();
		}
	}
	
	function get_periode_aktif(){
		$bulan=date('m');
		$tapel_aktif=get_tapel_aktif();
		$id_tapel=$tapel_aktif['id_mr_th_ajaran'];
		$sql="SELECT * FROM mr_periode_tapel WHERE SUBSTR(periode,6,2)='".$bulan."' AND id_tahun_ajaran='".$id_tapel."'";
		$query=mysql_query($sql);
		$num_row=mysql_num_rows($query);
		if ($num_row>0){
			return get_arr_query_row($sql);
		}else{
			return array();
		}
	}
	
	function get_komp_bulanan($id_tapel,$nis,$id_periode){
		$sql="select a.id_komponen,b.nama_komp,SUM(a.nominal) AS nominal from pembayaran_bulanan a 
				inner join mr_komp_pembayaran b	on a.id_komponen=b.id_komp 
				inner join mr_periode_tapel c on a.id_periode=c.id
				where a.status_bayar='tidak' and c.id_tahun_ajaran='".$id_tapel."' and a.nis='".$nis."' 
				and c.id<='".$id_periode."' GROUP BY a.`id_komponen`";
		//echo $sql;
		$query=mysql_query($sql);
		$num_row=mysql_num_rows($query);
		if ($num_row>0){
			//$hasil=get_arr_query($sql);
			return get_arr_query($sql);
		}else{
			return array();
		}
	}
	
	function get_tagihan_bulanan_tunggakan($id_tapel,$nis){
		$sql="select SUM(a.nominal) AS total from pembayaran_bulanan a 
				inner join mr_komp_pembayaran b	on a.id_komponen=b.id_komp 
				inner join mr_periode_tapel c on a.id_periode=c.id
				where a.status_bayar='tidak' and c.id_tahun_ajaran < '".$id_tapel."' and a.nis='".$nis."' ";
		//echo $sql;
		$query=mysql_query($sql);
		$num_row=mysql_num_rows($query);
		if ($num_row>0){
			$hasil= get_arr_query_row($sql);
			return $hasil['total'];
		}else{
			return 0;
		}
	}
	
	function get_tagihan_tahunan($id_tapel,$nis){
		$sql="select a.id_komponen,b.nama_komp,a.nominal from pembayaran_tahunan a inner join mr_komp_pembayaran b
				on a.id_komponen=b.id_komp where a.id_tapel='".$id_tapel."' and nis='".$nis."'
				and a.status_bayar='tidak'";
		//echo $sql;
		$query=mysql_query($sql);
		$num_row=mysql_num_rows($query);
		if ($num_row>0){
			return get_arr_query($sql);
		}else{
			return array();
		}
	}
	
	function get_tagihan_tahunan_tunggakan($id_tapel,$nis){
		$sql="select sum(a.nominal) as total from pembayaran_tahunan a left join mr_komp_pembayaran b
				on a.id_komponen=b.id_komp where a.id_tapel < '".$id_tapel."' and nis='".$nis."'
				and a.status_bayar='tidak'";
		//echo $sql;
		$query=mysql_query($sql);
		$num_row=mysql_num_rows($query);
		if ($num_row>0){
			$hasil= get_arr_query_row($sql);
			return $hasil['total'];
		}else{
			return 0;
		}
	}
	
	function get_tunggakan_total($id_tapel,$nis){
		$total_tahunan=get_tagihan_tahunan_tunggakan($id_tapel,$nis);
		$total_bulanan=get_tagihan_bulanan_tunggakan($id_tapel,$nis);
		//echo 'bulanan '.$total_bulanan;
		$hasil=$total_tahunan+$total_bulanan;
		return $hasil;
	}
	
	function get_tagihan_sekali($id_tapel,$nis){
		$sql="select a.id_komponen,b.nama_komp,a.nominal from tagihan_pemb_sekali a inner join mr_komp_pembayaran b
				on a.id_komponen=b.id_komp where a.id_tapel='".$id_tapel."' and nis='".$nis."'";
		//echo $sql;
		$query=mysql_query($sql);
		$num_row=mysql_num_rows($query);
		if ($num_row>0){
			return get_arr_query($sql);
		}else{
			return array();
		}
	}
	
	function get_bayar_sekali($nis,$id_komp){
		$sql="select IFNULL(SUM(nominal),0) as total from pembayaran_sekali 
			where nis='".$nis."' and id_komponen='".$id_komp."'";
		$query=mysql_query($sql);
		$num_row=mysql_num_rows($query);
		if ($num_row>0){
			$return =get_arr_query_row($sql);
			return $return['total'];
		}else{
			return array();
		}
	}
	
	function get_data_siswa(){
		$sql="select * from mr_siswa";
		$query=mysql_query($sql);
		$num_row=mysql_num_rows($query);
		if ($num_row>0){
			return get_arr_query($sql);
		}else{
			return array();
		}
	}
	function konvert_periode($str){
	$pecah=explode("-",$str);
	switch($pecah[1]) {
	case 1 : $nama_bulan = "Jan" ; break;
	case 2 : $nama_bulan = "Feb" ; break;
	case 3 : $nama_bulan = "Mar" ; break;
	case 4 : $nama_bulan = "Apr" ; break;
	case 5 : $nama_bulan = "Mei" ; break;
	case 6 : $nama_bulan = "Jun" ; break;
	case 7 : $nama_bulan = "Jul" ; break;
	case 8 : $nama_bulan = "Agus" ; break;
	case 9 : $nama_bulan = "Sep" ; break;
	case 10 : $nama_bulan = "Okt" ; break;
	case 11 : $nama_bulan = "Nov" ; break;
	case 12 : $nama_bulan = "Des" ; break;
	}
	return $nama_bulan." ".$pecah[0];
}

//buat kirim sms
function send_sms($notelp,$msgReply){
	   if (strlen($msgReply)<=160)
   {
      $query = "INSERT INTO outbox (DestinationNumber, TextDecoded, CreatorID, SenderID, Class) VALUES 
	  ('$notelp', '$msgReply', '', '', '-1')";
      mysql_query($query) or die(mysql_error());	  
   }
   else
   {
   $jmlSMS = ceil(strlen($msgReply)/153);
   $pecah  = str_split($msgReply, 153);
		 
   $query = "SHOW TABLE STATUS LIKE 'outbox'";
   $hasil = mysql_query($query);
   $data  = mysql_fetch_array($hasil);
   $newID = $data['Auto_increment'];
	
   $random = rand(1, 255);
   $headerUDH = sprintf("%02s", strtoupper(dechex($random)));
	
   for ($i=1; $i<=$jmlSMS; $i++)
   {
		
      $udh = "050003".$headerUDH.sprintf("%02s", $jmlSMS).sprintf("%02s", $i);
      $msg = $pecah[$i-1];
      if ($i == 1) $query = "INSERT INTO outbox (DestinationNumber, UDH, TextDecoded, ID, MultiPart, CreatorID, SenderID, Class)
	                         VALUES ('$notelp', '$udh', '$msg', '$newID', 'true', '', '', '-1')";
	  else $query = "INSERT INTO outbox_multipart(UDH, TextDecoded, ID, SequencePosition)
	                 VALUES ('$udh', '$msg', '$newID', '$i')";					  
      mysql_query($query); 
			
   }
   }
}
	
?>